"""
Case Type   : delete
Case Name   : 多表删除，delete xx from xxx 一些错误场景，合理报错
Create At   : 2024/07/25
Owner       : songjing20
Description :
    1.兼容B库下建表插入数据
    2.delete * from 表1，表2 where条件
    3.delete 表1 from 表2，表3 where条件,表1不存在
    4.不支持有limit、order by、returning子句
    5.清理环境
Expect      :
    1.成功
    2.失败，报错
    3.执行报错
    4.执行报错
    5.成功
History     :
"""

import os
import unittest

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class TestDelete(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_delete_0026'
        self.tb1_name = 't_delete_0026_1'
        self.tb2_name = 't_delete_0026_2'
        self.tb3_name = 't_delete_0026_3'
        self.log.info('-----创建B模式数据库-----')
        create_db_res = self.pri_sh.execut_db_sql(
            f"drop database if exists {self.db_name};"
            f"create database {self.db_name} dbcompatibility 'B';")
        self.log.info(create_db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_db_res,
                      "建库失败")

    def test_delete(self):
        text = '-----step1:兼容B库下建表;expect:成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f'''drop table if exists {self.tb1_name} cascade;
            drop table if exists {self.tb2_name} cascade;
            drop table if exists {self.tb3_name} cascade;
            create table {self.tb1_name}(col1 int,col2 varchar(10));
            create table {self.tb2_name}(col1 int,col2 varchar(10));
            create table {self.tb3_name}(col1 int,col2 varchar(10));
            insert into {self.tb1_name} values(1, '1'), (2, 'a');
            insert into {self.tb2_name} values(1, '1'), (2, 'b');
            insert into {self.tb3_name} values(1, '1'), (2, 'c');''',
            dbname=self.db_name)
        self.log.info(res)
        self.assertEqual(res.count(self.constant.CREATE_TABLE_SUCCESS),
                         3, f'创建表失败: {text}')
        self.assertEqual(res.count(self.constant.INSERT_SUCCESS_MSG),
                         3, f'创建表失败: {text}')

        text = '-----step2:delete * from 表1，表2 where条件;expect:失败-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f"delete * from {self.tb1_name} a, {self.tb2_name} b "
            f"where a.col1 = b.col1;", dbname=self.db_name)
        self.log.info(res)
        self.assertIn('ERROR:  syntax error at or near "*"', res,
                      f'执行失败: {text}')

        text = '-----step3:delete 表1 from 表2，表3 where条件,表1不存在;expect:报错-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f'''begin;
            drop table {self.tb1_name};
            delete {self.tb1_name} from {self.tb2_name} b, {self.tb3_name} c
            where b.col1 = c.col1;
            rollback;''', dbname=self.db_name)
        self.log.info(res)
        self.assertIn(f'ERROR:  relation "{self.tb1_name}" does not exist',
                      res, f'执行失败: {text}')

        text = '-----step4:不支持有limit、order by、returning子句;expect:执行报错-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(
            f'''delete a from {self.tb1_name} a left join {self.tb2_name} b 
            on a.col2=b.col2 limit 1;
            delete a from {self.tb1_name} a left join {self.tb2_name} b 
            on a.col2=b.col2 order by a.col2;
            delete a from {self.tb1_name} a left join {self.tb2_name} b 
            on a.col2=b.col2 returning *;
            delete {self.tb1_name} a from {self.tb1_name} a 
            left join {self.tb2_name} b on a.col2=b.col2 limit 1;''',
            dbname=self.db_name)
        self.log.info(res)
        self.assertEqual(res.count('ERROR:  syntax error'), 4, f'执行失败: {text}')

    def tearDown(self):
        text = '-----step5:清理环境; expect:成功-----'
        self.log.info(text)
        drop_res = self.pri_sh.execut_db_sql(
            f"drop database if exists {self.db_name};")
        self.log.info(drop_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res,
                      f'删库失败: {text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
